package com.crm.dao;


import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.crm.entity.Opportunity;
import com.crm.entity.PartSources;
import com.crm.entity.Parts;
import com.crm.entity.Sources;
import com.crm.util.ApplicationContext;

public class PartSourcesDAO {
	protected final Log logger = LogFactory.getLog(getClass());

	public  void insert(PartSources ss)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			session.saveOrUpdate(ss);
			tx.commit();
			return ;
		}
		catch (Exception e) {
			if(tx!=null)tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  List<PartSources> findAll(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			 List<PartSources> custObjs=new ArrayList<PartSources>();
			 List objs=null;
			Query query=session.createQuery("from PartSources where business_id=:busid");
			query.setInteger("busid", busid);
			objs=query.list();
			for(Object o:objs){
				custObjs.add((PartSources)o);
			}
			return custObjs;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  PartSources findById(int i) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			PartSources c=(PartSources)session.load(PartSources.class, i);
			return c;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	
	public  String setSrc(int busid,String rfNo) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			logger.info("REF NO IS "+rfNo);
			String querystr="from Opportunity where ref_num=:erf and business_id=:busid";
			Query qury=session.createQuery(querystr);
			qury.setString("erf", rfNo.trim());
			qury.setInteger("busid", busid);
			Object queryresult=qury.uniqueResult();
			Opportunity op=(Opportunity)queryresult;
			logger.info("ENQ ID IS "+op);
			int pid=op.getId();
			String src =getSources(pid,busid,session);
			return src;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	
	public  String getSources(int id,int busid, Session session) throws Exception {
	String retSrc="Plese Select";
		try{
			String q="select  DISTINCT sourcename from part_sources A ,sourcedet B where A.enqid ="+id+" and A.sourceid=B.id";
			System.out.println("Executing query "+q);
			Query query=session.createSQLQuery(q);
			List scrs=query.list();
			System.out.println("Returned scrcs "+scrs);
			StringBuilder ssrcs=new StringBuilder();
			for(Object s:scrs){
				ssrcs.append(s).append(",");
			}
			retSrc=ssrcs.substring(0,ssrcs.length()-1);
		}catch(Exception e){
			e.printStackTrace();
		}
		System.out.println("Returning srcs "+retSrc);
		return retSrc;
	}
	public  List<String> getPartSources(int enqid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try{
			String q="select  DISTINCT sourcename from part_sources A ,sourcedet B where A.enqid ="+enqid+" and A.sourceid=B.id and A.is_src_completed=false";
			System.out.println("Executing query "+q);
			Query query=session.createSQLQuery(q);
			List<String> scrs=query.list();
			System.out.println("Returned scrcs "+scrs);
			return scrs;
		}catch(Exception e){
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  List<Sources> getCompPartSources(int enqid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try{
			String q="from Sources where id in(select distinct A.sourceid from PartSources A ,Sources B where A.enqid =:enqid and A.sourceid=B.id and B.businessId=:busid)";
			System.out.println("Executing query "+q);
			Query query=session.createQuery(q);
			query.setInteger("enqid", enqid);
			query.setInteger("busid", busid);
			List<Sources> scrs=(List<Sources>)query.list();
			System.out.println("Returned scrcs "+scrs);
			return scrs;
		}catch(Exception e){
			throw e;
		}
		finally {
			session.close();
		}
	}
	
	public  List<PartSources> getSourcesByEnqid(int id,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			 List<PartSources> custObjs=new ArrayList<PartSources>();
			 List objs=null;
			Query query=session.createQuery("from PartSources where enqid=:id and business_id=:busid");
			query.setInteger("id", id);
			query.setInteger("busid", busid);
			objs=query.list();
			for(Object o:objs){
				custObjs.add((PartSources)o);
			}
			return custObjs;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	
	public  List<PartSources> getIsSrcCompleted(int businessid,int enqid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			 List<PartSources> custObjs=new ArrayList<PartSources>();
			 List objs=null;
			Query query=session.createQuery("from PartSources  where enqid=:enqid and business_id=:bid and is_src_completed=false");
			query.setInteger("enqid", enqid);
			query.setInteger("bid",businessid);
			objs=query.list();
			for(Object o:objs){
				custObjs.add((PartSources)o);
			}
			return custObjs;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  void deletePrtSrcInfo(int enqid,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
				tx = session.beginTransaction();
			String qry="Delete from PartSources o where enqid=:eqid and business_id=:busid";
			Query query=session.createQuery(qry);
			query.setInteger("eqid", enqid);
			query.setInteger("busid",busid);
			query.executeUpdate();
			tx.commit();
		}
		catch (Exception e) {
		if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}
	
	public  void save(PartSources ps) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			session.saveOrUpdate(ps);
			tx.commit();
			return ;
		}
		catch (Exception e) {
			if(tx!=null)tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  void saveList(List<PartSources> lps) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			for(PartSources ps:lps){
				session.saveOrUpdate(ps);
			}
			tx.commit();
			return ;
		}
		catch (Exception e) {
			if(tx!=null)tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  void updateSrc(int rid, int srcid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			String querystr="update PartSources set is_src_completed=true where enqid=:rid and sourceid=:srcid and business_id=:busid";
			Query qury=session.createQuery(querystr);
			qury.setInteger("rid", rid);
			qury.setInteger("srcid", srcid);
			qury.setInteger("busid", busid);
			qury.executeUpdate();
			tx.commit();
			return ;
		}
		catch (Exception e) {
			if(tx!=null)tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  void updateMailSent(int rid, int srcid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			String querystr="update PartSources set mail_sent=true where enqid=:rid and sourceid=:srcid and business_id=:busid";
			Query qury=session.createQuery(querystr);
			qury.setInteger("rid", rid);
			qury.setInteger("srcid", srcid);
			qury.setInteger("busid", busid);
			qury.executeUpdate();
			tx.commit();
			return ;
		}
		catch (Exception e) {
			if(tx!=null)tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}

	public List<PartSources> getMailNotSentSources(int refid, int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="from PartSources where enqid=:rid and  business_id=:busid and is_src_completed=false order by partid";
			Query qury=session.createQuery(querystr);
			qury.setInteger("rid", refid);
			qury.setInteger("busid", busid);
			List<PartSources> lps=(List<PartSources>)qury.list();
			return lps;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public List<PartSources> getSourceEnquiries(int sourceid, int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="from PartSources where sourceid=:sourceid and  business_id=:busid and is_src_completed=false order by partid";
			Query qury=session.createQuery(querystr);
			qury.setInteger("sourceid", sourceid);
			qury.setInteger("busid", busid);
			List<PartSources> lps=(List<PartSources>)qury.list();
			return lps;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  List<Parts> getPartsBySources(int enqid,int sourceid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From PartSources ps where ps.enqid=:enqid and ps.sourceid=:sourceid and business_id=:busid";
		     Query query=session.createQuery(querystr);
		     query.setInteger("enqid", enqid);
		     query.setInteger("sourceid", sourceid);
		     query.setInteger("busid", busid);
		     List<Parts>  parts=new ArrayList<Parts>();
		     List<Object> res=query.list();
		     if(res!=null)
		    	 for(Object o:res){
		    		 parts.add((Parts)o);
		    	 }
		     return parts;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}

	

}
